package com.happypets.utils;

import java.io.FileNotFoundException;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class DB {
	Connection conn = null;
	Statement sta = null;
	ResultSet rs = null;

	ArrayList<String[]> al;

	/** 连接(localhost);并未写RESULTSET的语句 
	 * 链接新的数据库必须更改此方法
	 * ***/
	public void getConnection() {
		///////////////////////////////////
		/////////mysql/////////////////////
//		String user = "root";
//		String password = "fsh950905";
//		String url = "jdbc:mysql://localhost:3306/web";
//		String driver = "com.mysql.jdbc.Driver";
		
		
		///////////////////////////////////
		//////////oracle///////////////////
/*		String user = "feng";
		String password="fsh950905";
		String url = "jdbc:oracle:thin:@localhost:1521:orcl";
		String driver = "oracle.jdbc.driver.OracleDriver";*/
//		oracle.jdbc.driver.OracleDriver;
		try {
		//	Class.forName(driver);// 加载驱动
			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
		//	conn = DriverManager.getConnection(url, user, password);// 获取连接
			conn=DriverManager.getConnection("proxool.fengProxool"); 
			sta = conn.createStatement();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/*** 执行命令（增删改查） ***/
	public boolean executeSql(String sql){

		getConnection();
		int flag=-1;
		try {
			flag=sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return flag!=-1;
	}
	
	/**
	 * 执行
	 * @param sql
	 * @return
	 */
	public int excuteQuery(String sql){
		getConnection();
		int i = 0;
		try {
			i = sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		return i;
	}
	
	public List<Map<String,Object>> getMapInList(String sql){
		
		List<Map<String, Object>> lm = new LinkedList<Map<String,Object>>();
		getConnection();
		
		try {
			rs = sta.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();// 动态获取字段总数需要获取结果集结构对象

			int num = rsmd.getColumnCount();// 获取字段总数
			
			while(rs.next()){
				Map<String, Object> map = new HashMap<String,Object>();
				for (int i = 1; i <= num; i++) {
					
					String key = rsmd.getColumnName(i);
					Object value = rs.getObject(i);
					
					
//					System.out.println(key+"---"+value);
					
					map.put(key, value);
					
				}
				
				lm.add(map);
				
			}
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		
		return lm;
	}

	/***
	 * 返回LinkedList<HashMap<String,String>>   现在不能用
	 * @throws FileNotFoundException 
	 **/
	public LinkedList<HashMap<String, String>> query() throws FileNotFoundException {


		LinkedList<HashMap<String, String>> ll = new LinkedList<HashMap<String, String>>();
		getConnection();
		try {
			rs = sta.executeQuery("select * from employee");
			ResultSetMetaData rsmd = rs.getMetaData();// 动态获取字段总数需要获取结果集结构对象

			int num = rsmd.getColumnCount();// 获取字段总数
            
			while (rs.next()) {/*
				
				HashMap<String, String> hm = new HashMap<String, String>();
				switch(rs.getType() ){
				case  java.sql.Types.BINARY :
					InputStream  in=rs.getBinaryStream("img");
					File file=new File("");
					FileOutputStream out=new FileOutputStream(file);
					byte img[]=new byte[512];
					break;
				case  java.sql.Types.LONGVARBINARY:
					
					
				break;
				}
				 
				for (int i = 1; i < num + 1; i++) {
					String key = rsmd.getColumnName(i);
					String value = rs.getString(i);
					 
					hm.put(key, value);
				}
				ll.add(hm);
			*/}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return ll;
	}

	/***
	 * 计算页数 Mysql
	 * 
	 * 
	 * **/
	public ArrayList<String[]> pageSearch(int pageShowCount,int pageNum,String sql){

			String newsql=sql+" limit "+(pageShowCount*(pageNum-1))+","+pageShowCount;
			return arrquery(newsql);
		
	}
	
	
	
	
	/**
	 * 返回arrayList
	 * @throws SQLException 
	 * */
	public ArrayList<String[]> arrquery(String sql,int g) throws SQLException {


		ArrayList<String[]> al = new ArrayList<String[]>();
		getConnection();
			rs = sta.executeQuery(sql);

			ResultSetMetaData rsmd = rs.getMetaData();
			int num = rsmd.getColumnCount();

			while (rs.next()) {
				String[] t = new String[num];
				for (int i = 1; i < num + 1; i++) {

					t[i - 1] = rs.getString(i);
				}
				al.add(t);
			}


		close();


		return al;
	}

	/**
	 * 返回arrayList
	 * */
	public ArrayList<String[]> arrquery(String sql) {


		ArrayList<String[]> al = new ArrayList<String[]>();
		getConnection();
		try {
			rs = sta.executeQuery(sql);

			ResultSetMetaData rsmd = rs.getMetaData();
			int num = rsmd.getColumnCount();

			while (rs.next()) {
				String[] t = new String[num];
				for (int i = 1; i < num + 1; i++) {

					// al.add(rs.getString(i));
					t[i - 1] = rs.getString(i);
				}
				al.add(t);
			}

		} catch (SQLException e) {
		}finally{
			close();
		}

		return al;
	}
	
	/**获取单独一行数据，返回String[]
	 * @throws SQLException **/
	public String[] strquery(String sql,int f) throws SQLException{

		getConnection();
		String[] str=null;
			rs=sta.executeQuery(sql);
			/*****极其重要****/
			rs.next();
			/*********/
			ResultSetMetaData rsmd=rs.getMetaData();
			
			int length=rsmd.getColumnCount();
			
			str=new String[length];
			
			for (int i = 1; i < str.length+1; i++) {
				str[i-1]=rs.getString(i);
			}
			
			close();
			

		
		return str;
		
	}

	/**获取单独一行数据，返回String[]**/
	public String[] strquery(String sql){

		getConnection();
		String[] str=null;
		try {
			rs=sta.executeQuery(sql);
			/*****极其重要****/
			rs.next();
			/*********/
			ResultSetMetaData rsmd=rs.getMetaData();
			
			int length=rsmd.getColumnCount();
			
			str=new String[length];
			
			for (int i = 1; i < str.length+1; i++) {
				str[i-1]=rs.getString(i);
			}
			
			
			
			
		} catch (SQLException e) {
			System.out.println("空的");
		}finally{
			close();
		}
		
		return str;
		
	}

	/***获取单个东西
	 * 返回String
	 * ***/
	public String getStr(String sql){
		getConnection();
		try {
			rs=sta.executeQuery(sql);
			rs.next();
			return rs.getString(1);
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
			
		}
		
		
		return null;
		
	}
	
	
	
	/***执行批处理***/
	public int[] batching(String[] sql){
		
		getConnection();
		
		int[] i = null;
		
		for(String query:sql){
			
			try {
				sta.addBatch(query);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		try {
			i = sta.executeBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}
		
		return i;
		
		
		
		
//		return true;
	}
	
	
	/*****
	 * 通过sql语句进行登录，返回一个字符数组其中第一个为判断是否成功
	 * 如果成功则为1失败为-1
	 * 第二个为权限
	 * 1为管理员
	 * 2为普通
	 * ***/
	public String[] login(String sql){
//		getConnection();
		String power="-1";
//		String info="-1";
//		String[] s=strquery(sql);
		ArrayList<String[]> al=arrquery(sql);
/*		for(String r:s){
			System.out.println("r is "+r);
		}
		System.out.println("length ="+s.length);*/
		if(al.size()==0){
			//失败
			return new String[]{"-1",power};
		}
		
		//成功
//		power=s[2];
		power=al.get(0)[2];
		return new String[]{"1",power};
		
//		try {
//			rs=sta.executeQuery(sql);
//		} catch (SQLException e) {
////			e.printStackTrace();
////			System.out.println("false 1");
////			return new String[]{"-1","-1"};//失败
//		}
		
//		try {
//			rs.next();
//			info=rs.getString(3);//getString 从零开始
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
////			e.printStackTrace();
////			System.out.println("false 2");
//		}
	}
	
	/*** 关闭 ***/

	public void close() {
		
		try {
			
			
			if(!rs.isClosed()){
				rs.close();
			}
			
			if(!sta.isClosed()){
				sta.close();
			}
			
			if(!conn.isClosed()){
				conn.close();
			}
			
			
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

	}
	
	

	
	
	public boolean excutePrepare(String sql,ArrayList<String> list){
		getConnection();
		try {

			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=1;i<=list.size();i++){
				ps.setObject(i, list.get(i-1));
			}
			int flag = ps.executeUpdate();
			if(flag == 1){
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return false;
	}
	
	
	public static void main(String[] args) {
		DB db = new DB();
		
//		String sql = "select * from test;";
		db.getConnection();
		
		
	}

	
	
	
}
